﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using DataHelper;
using Utils;
namespace DAL
{
    public class SalesmanDao
    {
        public bool Insert(string tableName, SortedDictionary<string, string> sdList)
        {
            return new BuiltSQL().InsertTable(tableName, sdList);
        }
        public bool Update(string tableName, SortedDictionary<string, string> sdList)
        {
            return new BuiltSQL().UpdateTable(tableName, sdList);
        }
        public bool ExecSQL(string strSQL)
        {
            return SQLHelper.ExecuteSqlWithTransaction(strSQL);

        }
        public DataTable GetLists(string strSQL)
        {
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
        }
        public bool DeleteOneInfo(string saleManID)
        {
            return ExecSQL("delete from Admin_Salesman where SalemanID=" + saleManID);
        }
        public bool DeleteOneInfo(string empCode, string stateName)
        {
            return ExecSQL("Delete from Admin_Salesman Where StateName= '" + stateName + "' and EmpCode='" + empCode + "'");
        }
        //通过ID获取整条信息
        public DataTable GetOneInfo(string saleManID)
        {
            return GetLists("Select * from Admin_Salesman Where SaleManID=" + saleManID);
        }
        /// <summary>
        /// 获取整条信息
        /// </summary>
        /// <param name="empCode">工号：工号可能多人使用</param>
        /// <param name="stateName">状态：在职、离职</param>
        /// <returns>DataTable</returns>
        public DataTable GetOneInfo(string empCode, string stateName)
        {
            if (string.IsNullOrWhiteSpace(stateName))
                return GetLists("Select * from Admin_Salesman Where EmpCode='" + empCode + "' order by statename desc,JoinTime DESC");
            else
                return GetLists("Select * from Admin_Salesman Where StateName= '" + stateName + "' and EmpCode='" + empCode + "' order by statename desc,JoinTime DESC");
        }
        public DataTable GetOneInfoByName(string empName, string stateName)
        {
            if (!string.IsNullOrWhiteSpace(stateName))
                return GetLists("Select * from Admin_Salesman Where StateName='" + stateName + "' and EmpName='" + empName + "'  order by statename desc,JoinTime DESC");
            else
                return GetLists("Select * from Admin_Salesman Where EmpName='" + empName + "'  order by statename desc,JoinTime DESC");
        }

        public bool IsExistEmpCode(string empCode, string stateName)
        {
            DataTable dt = GetOneInfo(empCode, stateName);
            if (dt.Rows.Count == 1)//如果存在该用户编号
                return true;
            else//否则
                return false;
        }
        //列表SQL
        public string GetSQL(string serWord, string bTime, string eTime, string stateName, string timeType)
        {
            StringBuilder st = new StringBuilder("Select * From Admin_Salesman Where ");

            if (stateName == "全部")            
                st.Append(" 1=1 ");
            else
                st.Append(" StateName='" + stateName + "' ");
            if (!string.IsNullOrWhiteSpace(serWord))
                st.Append(" And (EmpCode='" + serWord + "' Or EmpName Like '%" + serWord + "%' OR FromCompanyName Like '%" + serWord + "%' Or DutyName  Like '%" + serWord + "%' Or DeptName  Like '%" + serWord + "%')");
            else
            {              
               
                switch (timeType)
                {
                    case "JoinTime":
                        if (RegexRule.IsDate(bTime)) st.Append("and JoinTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) st.Append("and JoinTime <='" + eTime + "'");
                        break;
                    case "InputTime":
                        if (RegexRule.IsDate(bTime)) st.Append("and InputTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) st.Append("and InputTime <='" + eTime + "'");
                        break;
                    case "OutTime":
                        if (RegexRule.IsDate(bTime)) st.Append("and OutTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) st.Append("and OutTime <='" + eTime + "'");
                        break;
                    case "InTime":
                        if (RegexRule.IsDate(bTime)) st.Append("and InTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) st.Append("and InTime <='" + eTime + "'");
                        break;
                    case "DayNum":
                        if (RegexRule.IsDate(bTime)) st.Append("and DayNum >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) st.Append("and DayNum <='" + eTime + "'");
                        break;
                    case "BackAmountTime":
                        if (RegexRule.IsDate(bTime)) st.Append("and BackAmountTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) st.Append("and BackAmountTime <='" + eTime + "'");
                        break;                  
                  
                }
            }
            st.Append(" order by StateName DESC,LeaveTime Desc,EmpCode ASC");
            return st.ToString();
        }
        public DataTable GetLists(string serWord, string bTime, string eTime, string stateName, string timeType)
        {
            return GetLists(GetSQL(serWord,  bTime, eTime, stateName, timeType));
        }


        #region 职位变动
        //通过SaleManID 获取变动信息  
        public DataTable GetChangeLists(string saleManID)
        {
            return GetLists("Select * From Admin_Salesman_Change Where SaleManID=" + saleManID);
        }
        public DataTable GetChangeLists(string empCode, string bTm, string eTm)
        {
            return GetLists(GetSQL(empCode, bTm, eTm));
        }
        public DataTable GetChangeOneInfo(string changeID)
        {
            return GetLists("Select * from Admin_Salesman_Change Where ChangeID=" + changeID);

        }
        public bool DeleteChangeOneInfo(string changeID)
        {
            return ExecSQL("Delete from Admin_Salesman_Change Where ChangeID=" + changeID);
        }
        //促销员职位变动列表SQL
        public string GetSQL(string empCode, string bTime, string eTime)
        {
            StringBuilder st = new StringBuilder("Select * From Admin_Salesman_Change Where 1=1 ");
            if (!string.IsNullOrWhiteSpace(empCode))
                st.Append(" And (EmpCode='" + empCode + "' Or EmpName Like '%" + empCode + "%'  Or DeptName  Like '%" + empCode + "%')");
            else
            {
                if (!string.IsNullOrWhiteSpace(bTime) && RegexRule.IsDate(bTime))
                    st.Append(" And ChangeTime>='" + bTime + "'");
                if (!string.IsNullOrWhiteSpace(eTime) && RegexRule.IsDate(eTime))
                    st.Append(" And ChangeTime<='" + eTime + "'");
            }
            st.Append(" order by EmpCode ASC");
            return st.ToString();
        }

        #endregion

        #region 离职信息
        public DataTable GetLeaveOneInfo(string leaveID)
        {
            return GetLists("Select * from Admin_Salesman_Leave Where LeaveID=" + leaveID);

        }
        public bool DeleteLeaveOneInfo(string leaveID)
        {
            return ExecSQL("Delete from Admin_Salesman_Leave Where LeaveID=" + leaveID);
        }
        public DataTable GetLeaveLists(string saleManID)
        { return GetLists("Select * From Admin_Salesman_Leave Where SaleManID=" + saleManID); }
        public DataTable GetLeaveLists(string empCode, string bTime, string eTime, string timeType)
        {
            return GetLists(GetLeaveSQL(empCode, bTime, eTime,  timeType));

        }
        public string GetLeaveSQL(string empCode, string bTime, string eTime, string timeType)
        {
            StringBuilder st = new StringBuilder("Select * From Admin_Salesman_Leave Where 1=1 ");
            //string strSQL = "Select * From Admin_Salesman Where 1=1 ";
            if (!string.IsNullOrWhiteSpace(empCode))
                st.Append(" And (EmpCode='" + empCode + "' Or EmpName Like '%" + empCode + "%'  Or DeptName  Like '%" + empCode + "%')");
            else
            {
                switch (timeType)
                {
                    case "JoinTime":
                        if (RegexRule.IsDate(bTime)) st.Append("and JoinTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) st.Append("and JoinTime <='" + eTime + "'");
                        break;
                    case "InputTime":
                        if (RegexRule.IsDate(bTime)) st.Append("and InputTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) st.Append("and InputTime <='" + eTime + "'");
                        break;
                    case "LeaveTime":
                        if (RegexRule.IsDate(bTime)) st.Append("and LeaveTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) st.Append("and LeaveTime <='" + eTime + "'");
                        break;
                    case "BackAmountTime":
                        if (RegexRule.IsDate(bTime)) st.Append("and BackAmountTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) st.Append("and BackAmountTime <='" + eTime + "'");
                        break;
                    case "DayNum":
                        if (RegexRule.IsDate(bTime)) st.Append("and DayNum >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) st.Append("and DayNum <='" + eTime + "'");
                        break;

                }
            }
            st.Append(" order by LeaveTime DESC, EmpCode ASC");
            return st.ToString();
        }

        #endregion

        #region 奖惩

        public bool DeleteRewardsOneInfo(string rewardsID)
        {
            return ExecSQL("Delete from Admin_Salesman_Rewards Where RewardsID=" + rewardsID);
        }
        //获取一条记录
        public DataTable GetRewardsOneInfo(string rewardsID)
        {
            return GetLists("Select * from Admin_Salesman_Rewards Where RewardsID=" + rewardsID);
        }
        public DataTable GetRewardsLists(string saleManID)
        {
            return GetLists("Select * From Admin_Salesman_Rewards Where SaleManID=" + saleManID);
        }
        public DataTable GetRewardsLists(string deptID, string serWord, string bTime, string eTime, string type)
        {        
            return GetLists(GetRewardsSQL(deptID, serWord, bTime, eTime, type));
        }
        //SQL
        public string GetRewardsSQL(string deptID, string serWord, string bTime, string eTime,string type)
        {
            StringBuilder st = new StringBuilder("Select * From Admin_Salesman_Rewards Where 1=1 ");
            if (!string.IsNullOrWhiteSpace(serWord))
                st.Append(" And (EmpCode='" + serWord + "' Or EmpName Like '%" + serWord + "%')");
            else
            {
                if (deptID != "" && deptID != "0")
                    st.Append(" And DeptName like '%" + deptID + "%'");
            }
            if (type!="0")
                st.Append(" And RewardsType ='" + type + "'");
            if (RegexRule.IsDate(bTime))
                st.Append(" And RewardsTime>='" + bTime + "'");
            if (RegexRule.IsDate(eTime))
                st.Append(" And RewardsTime<='" + eTime + "'");
            st.Append(" order by RewardsTime Desc,EmpCode ASC");
            return st.ToString();
        }
        #endregion

        #region 统计
        public DataTable GetStatisticBySalesmen(string typeName, string bTime, string eTime)
        {
            string strSQL = "select top 200 EmpName as 姓名,sum(RewardsAmount) as 合计金额 from Admin_Salesman_Rewards where 1=1 ";
            if (typeName == "全部")
            // strSQL += " AND RewardsAmount>0 ";
            //else if (typeName == "惩罚")
            //   strSQL += " AND RewardsAmount<0 ";
            //else
            {
                strSQL = "select top 200 EmpName 姓名,sum(case when RewardsAmount>0 then RewardsAmount else 0 end ) 奖励 ,sum(case when RewardsAmount<0 then RewardsAmount else 0 end ) 惩罚,sum(RewardsAmount) 合计金额 from Admin_Salesman_Rewards  where 1=1 ";
            }
            else
            {
                    strSQL += " AND RewardsType='" + typeName + "' ";
            }
            if (RegexRule.IsDate(bTime) && RegexRule.IsDate(eTime))
                strSQL += " And (RewardsTime>='" + bTime + "' And RewardsTime<='" + eTime + "')";
            else if (RegexRule.IsDate(bTime) && !RegexRule.IsDate(eTime))
                strSQL += " And RewardsTime>='" + bTime + "'";
            else if (!RegexRule.IsDate(bTime) && RegexRule.IsDate(eTime))
            { //按年，统计12个月份的数据
                DateTime dt = DateTime.Parse(eTime);
                int y = dt.Year;

                StringBuilder sb = new StringBuilder("select top 200 EmpName as 姓名,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='1' then RewardsAmount else 0 end ) '1月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='2' then RewardsAmount else 0 end ) '2月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='3' then RewardsAmount else 0 end ) '3月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='4' then RewardsAmount else 0 end ) '4月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='5' then RewardsAmount else 0 end ) '5月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='6' then RewardsAmount else 0 end ) '6月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='7' then RewardsAmount else 0 end ) '7月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='8' then RewardsAmount else 0 end ) '8月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='9' then RewardsAmount else 0 end ) '9月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='10' then RewardsAmount else 0 end ) '10月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='11' then RewardsAmount else 0 end ) '11月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='12' then RewardsAmount else 0 end ) '12月' ,");
                sb.Append("sum(RewardsAmount) 合计金额  from Admin_Salesman_Rewards  where substring(RewardsTime,1,4)=");

                sb.Append("'" + y + "'");
                strSQL = sb.ToString();
            }

            strSQL += " group by  EmpName order by 合计金额 Desc";

            return GetLists(strSQL);
        }

        public DataTable GetStatisticByDept(string typeName, string bTime, string eTime)
        {
            string strSQL = "select DeptName as 部门,sum(RewardsAmount) as 合计金额 from Admin_Salesman_Rewards where 1=1";

            if (RegexRule.IsDate(bTime) && RegexRule.IsDate(eTime))
                strSQL += " And (RewardsTime>='" + bTime + "' And RewardsTime<='" + eTime + "')";
            else if (RegexRule.IsDate(bTime) && !RegexRule.IsDate(eTime))
                strSQL += " And RewardsTime>='" + bTime + "'";
            else if (!RegexRule.IsDate(bTime) && RegexRule.IsDate(eTime))
            { //按年，统计12个月份的数据
                DateTime dt = DateTime.Parse(eTime);
                int y = dt.Year;

                StringBuilder sb = new StringBuilder("select DeptName as 部门,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='1' then RewardsAmount else 0 end ) '1月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='2' then RewardsAmount else 0 end ) '2月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='3' then RewardsAmount else 0 end ) '3月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='4' then RewardsAmount else 0 end ) '4月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='5' then RewardsAmount else 0 end ) '5月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='6' then RewardsAmount else 0 end ) '6月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='7' then RewardsAmount else 0 end ) '7月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='8' then RewardsAmount else 0 end ) '8月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='9' then RewardsAmount else 0 end ) '9月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='10' then RewardsAmount else 0 end ) '10月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='11' then RewardsAmount else 0 end ) '11月' ,");
                sb.Append("sum(case when substring(RewardsTime,6,2)='12' then RewardsAmount else 0 end ) '12月' ,");
                sb.Append("sum(RewardsAmount) 合计金额  from Admin_Salesman_Rewards  where substring(RewardsTime,1,4)=");

                sb.Append("'" + y + "'");
                strSQL = sb.ToString();
            }
            //if (typeName == "奖励")
            //    strSQL += " AND RewardsAmount>0 ";
            //else if (typeName == "惩罚")
            //    strSQL += " AND RewardsAmount<0 ";
           //
            if (typeName != "全部")
                strSQL += " AND RewardsType='" + typeName + "' ";
            strSQL += " group by  DeptName order by 合计金额 Desc";

            return GetLists(strSQL);
        }
        #endregion


        #region 进场信息
        public DataTable GetInOneInfo(string InID)
        {
            return GetLists("Select * from Admin_Salesman_In Where InID=" + InID);
        }
        public bool DeleteInOneInfo(string InID)
        {
            return ExecSQL("Delete from Admin_Salesman_In Where InID=" + InID);
        }
        public DataTable GetInOneInfo(string statu,string empCode)
        {
            return SQLHelper.ExecuteSqlGetDrToDt("select * from Admin_Salesman_In Where EmpCode='"+empCode+"' and Statu='Y'");
        }
        //判断是否存在有效地入场信息
        public bool IsExistInInfo(string empCode)
        {
            DataTable dt = GetInOneInfo(empCode,"Y");
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }

        public bool UpdateInStatu(string saleManID,string statu)
        {
            string strSQL = "update Admin_Salesman_In Set Statu='" + statu + "' Where SaleManID=" + saleManID;
          return  SQLHelper.ExecuteSql(strSQL);
        }
        public DataTable GetInLists(string saleManID)
        { return GetLists("Select * From Admin_Salesman_In Where SaleManID=" + saleManID); }
        //获取复职列表
        public DataTable GetInLists(string empCode, string deptName,string bTime, string eTime, string timeType)
        {
            return GetLists(GetInSQL(empCode, deptName, bTime,  eTime,  timeType).ToString());

        }
        public string GetInSQL(string empCode, string deptName,string bTime, string eTime, string timeType)
        {
            StringBuilder st = new StringBuilder("Select * From Admin_Salesman_In Where 1=1 ");
            //string strSQL = "Select * From Admin_Salesman Where 1=1 ";
            if (!string.IsNullOrWhiteSpace(empCode))
                st.Append(" And (EmpCode='" + empCode + "' Or EmpName Like '%" + empCode + "%')");
            else
            {
                if (!string.IsNullOrWhiteSpace(deptName))
                    st.Append(" And  DeptName  Like '%" + deptName + "%'");
                switch (timeType)
                {
                    case "BeginTime":
                        if (RegexRule.IsDate(bTime)) st.Append("and BeginTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) st.Append("and BeginTime <='" + eTime + "'");
                        break;
                    case "EndTime":
                        if (RegexRule.IsDate(bTime)) st.Append("and EndTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) st.Append("and EndTime <='" + eTime + "'");
                        break;
                    case "InputTime":
                        if (RegexRule.IsDate(bTime)) st.Append("and InputTime >='" + bTime + "'");
                        if (RegexRule.IsDate(eTime)) st.Append("and InputTime <='" + eTime + "'");
                        break;                

                }
            }
            st.Append(" order by  Statu Desc,BeginTime DESC,DeptName ASC");
            return st.ToString();
        }

        #endregion
    }
}
